ORDER BY

The order by command is used to sort records in ascending and descending order. When ordering, asc specifies ascending order (which is the default), and desc specifies descending order.

asc

Example:

 select * from employees order by last_name asc;

This query orders employees by last_name in ascending alphabetical order.

Result:

desc

Example:

select * from employees order by last_name desc;

This query orders employees by last_name in descending alphabetical order.

Result:

Ecto query for order by

order_by/3

Expression

In Ecto, the order_by/3 function is used to alphabetically order records.

Example:

asc

HR.Employee
|> order_by(asc: :last_name)
|> HR.Repo.all()

Result:


IEx(18)> HR.Employee |> order_by(asc: :last_name) |> HR.Repo.all()

[
  %HR.Employee{
    __meta__: #Ecto.Schema.Metadata<:loaded, "employees">,
    employee_id: 105,
    first_name: "David",
    last_name: "Austin",
    email: "david.austin@sqltutorial.org",
    phone_number: "590.423.4569",
    hire_date: ~D[1997-06-25],
    salary: Decimal.new("4800.00"),
    manager_id: 103,
    job_id: 9,
    department_id: 6
  },
  %HR.Employee{
    __meta__: #Ecto.Schema.Metadata<:loaded, "employees">,
    employee_id: 204,
    first_name: "Hermann",
    last_name: "Baer",
    email: "hermann.baer@sqltutorial.org",
    phone_number: "515.123.8888",
    hire_date: ~D[1994-06-07],
    salary: Decimal.new("10000.00"),
    manager_id: 101,
    job_id: 12,
    department_id: 7
  },
  %HR.Employee{
    __meta__: #Ecto.Schema.Metadata<:loaded, "employees">,
    employee_id: 116,
    first_name: "Shelli",
    last_name: "Baida",
    email: "shelli.baida@sqltutorial.org",
    phone_number: "515.127.4563",
    hire_date: ~D[1997-12-24],
    salary: Decimal.new("2900.00"),
    manager_id: 114,
    job_id: 13,
    department_id: 3
  },
  %HR.Employee{
    __meta__: #Ecto.Schema.Metadata<:loaded, "employees">,
    employee_id: 192,
    first_name: "Sarah",
    last_name: "Bell",
    email: "sarah.bell@sqltutorial.org",
    phone_number: "650.501.1876",
    hire_date: ~D[1996-02-04],
    salary: Decimal.new("4000.00"),
    manager_id: 123,
    job_id: 17,
    department_id: 5
  },
  %HR.Employee{
    __meta__: #Ecto.Schema.Metadata<:loaded, "employees">,
    employee_id: 110,
    first_name: "John",
    last_name: "Chen",
    email: "john.chen@sqltutorial.org",
    phone_number: "515.124.4269",
    hire_date: ~D[1997-09-28],
    salary: Decimal.new("8200.00"),
    manager_id: 108,
    job_id: 6,
    department_id: 10
  },
  %HR.Employee{
    __meta__: #Ecto.Schema.Metadata<:loaded, "employees">,
    employee_id: 119,
    first_name: "Karen",
    last_name: "Colmenares",
    email: "karen.colmenares@sqltutorial.org",
    phone_number: "515.127.4566",
    hire_date: ~D[1999-08-10],
    salary: Decimal.new("2500.00"),
    manager_id: 114,
    job_id: 13,
    department_id: 3
  },
  %HR.Employee{
    __meta__: #Ecto.Schema.Metadata<:loaded, "employees">,
    employee_id: 102,
    first_name: "Lex",
    last_name: "De Haan",
    email: "lex.de haan@sqltutorial.org",
    phone_number: "515.123.4569",
    hire_date: ~D[1993-01-13],
    salary: Decimal.new("17000.00"),
    manager_id: 100,
    job_id: 5,
    department_id: 9
  }

<!-- and so on -->

]

you can see that last_name is starts from Austin.

desc

HR.Employee
|> order_by(desc: :last_name)
|> HR.Repo.all()

Result:

IEx(20)> HR.Employee |> order_by(desc: :last_name) |> HR.Repo.all()

[
  %HR.Employee{
    __meta__: #Ecto.Schema.Metadata<:loaded, "employees">,
    employee_id: 200,
    first_name: "Jennifer",
    last_name: "Whalen",
    email: "jennifer.whalen@sqltutorial.org",
    phone_number: "515.123.4444",
    hire_date: ~D[1987-09-17],
    salary: Decimal.new("4400.00"),
    manager_id: 101,
    job_id: 3,
    department_id: 1
  },
  %HR.Employee{
    __meta__: #Ecto.Schema.Metadata<:loaded, "employees">,
    employee_id: 120,
    first_name: "Matthew",
    last_name: "Weiss",
    email: "matthew.weiss@sqltutorial.org",
    phone_number: "650.123.1234",
    hire_date: ~D[1996-07-18],
    salary: Decimal.new("8000.00"),
    manager_id: 100,
    job_id: 19,
    department_id: 5
  },
  %HR.Employee{
    __meta__: #Ecto.Schema.Metadata<:loaded, "employees">,
    employee_id: 123,
    first_name: "Shanta",
    last_name: "Vollman",
    email: "shanta.vollman@sqltutorial.org",
    phone_number: "650.123.4234",
    hire_date: ~D[1997-10-10],
    salary: Decimal.new("6500.00"),
    manager_id: 100,
    job_id: 19,
    department_id: 5
  },
  %HR.Employee{
    __meta__: #Ecto.Schema.Metadata<:loaded, "employees">,
    employee_id: 112,
    first_name: "Jose Manuel",
    last_name: "Urman",
    email: "jose manuel.urman@sqltutorial.org",
    phone_number: "515.124.4469",
    hire_date: ~D[1998-03-07],
    salary: Decimal.new("7800.00"),
    manager_id: 108,
    job_id: 6,
    department_id: 10
  },
  %HR.Employee{
    __meta__: #Ecto.Schema.Metadata<:loaded, "employees">,
    employee_id: 117,
    first_name: "Sigal",
    last_name: "Tobias",
    email: "sigal.tobias@sqltutorial.org",
    phone_number: "515.127.4564",
    hire_date: ~D[1997-07-24],
    salary: Decimal.new("2800.00"),
    manager_id: 114,
    job_id: 13,
    department_id: 3
  }

<!-- and so on -->

]

You can see that last_name starts from Whalen.

Keywords

asc

HR.Repo.all(from c in HR.Employee, order_by: [asc: :last_name])

desc

HR.Repo.all(from c in HR.Employee, order_by: [desc: :last_name])

In both examples, c is the reference variable representing HR.Employee. For more on reference variables, see Aliases in Ecto.

Back to Homepage